package com.sz.biz.logistics.serialno.service.impl;

import com.sz.biz.common.utils.AliSMSPhonesUtil;
import com.sz.biz.logistics.core.dto.ServiceDto;
import com.sz.biz.logistics.core.entity.Product;
import com.sz.biz.logistics.core.entity.ProductGroup;
import com.sz.biz.logistics.core.service.ProductGroupService;
import com.sz.biz.logistics.core.service.ProductService;
import com.sz.biz.logistics.core.service.ServiceService;
import com.sz.biz.logistics.serialno.algorithm.AlgorithmFactory;
import com.sz.biz.logistics.serialno.algorithm.VerificationCodeAlgorithm;
import com.sz.biz.logistics.serialno.constants.BillNumberConstants.*;
import com.sz.biz.logistics.serialno.constants.BillNumberErrorCodes;
import com.sz.biz.logistics.serialno.constants.BillNumberMessagesCodes;
import com.sz.biz.logistics.serialno.constants.WaybillPrefixCollectionConstants;
import com.sz.biz.logistics.serialno.dto.*;
import com.sz.biz.logistics.serialno.entity.SerialNoRule;
import com.sz.biz.logistics.serialno.entity.SerialNoRuleDetails;
import com.sz.biz.logistics.serialno.entity.SerialNoWarnNotice;
import com.sz.biz.logistics.serialno.service.*;
import com.sz.biz.logistics.serialno.utils.EmailValid;
import com.sz.biz.logistics.serialno.utils.PatternStringToNumber;
import com.sz.biz.logistics.serialno.utils.SerialNumberRangeResolve;
import com.sz.biz.logistics.serialno.utils.TelPhoneValid;
import com.sz.common.base.constants.TemplateConstants;
import com.sz.common.base.dao.ParamData;
import com.sz.common.base.dto.QResultDto;
import com.sz.common.base.dto.QueryParams;
import com.sz.common.base.exception.Exceptions;
import com.sz.common.base.i18n.I18nMessageHelper;
import com.sz.common.base.service.AbstractService;
import com.sz.common.base.utils.JsonUtils;
import com.sz.common.base.utils.StringUtils;
import com.sz.common.core.constants.SysErrorCodes;
import com.sz.common.core.system.service.SysMailService;
import com.sz.common.core.system.service.SysSmsService;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import java.io.InputStream;
import java.util.*;
import java.util.stream.Collectors;
import java.util.stream.Stream;

import static com.sz.biz.common.constants.SymbolConstants.SEPERATOR_SPACE;
import static com.sz.biz.logistics.serialno.algorithm.AlgorithmFactory.algorithmMap;


/**
 * Function: 单号规则相关功能实现 <br>
 * Author: Johnboy <br>
 * Date: 2017-12-07 17:10:00
 *
 * @author Johnboy
 */
@Service
@Transactional(propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
public class NoRuleServiceImpl extends AbstractService implements NoRuleService {
    
    private static final int MAX_SIZE = 500;
    private static final int EXAMPLE_NUMBERS = 5;
    public static final int RANGE = 2;
    public static final int ARRAY_LENGTH = 2;
    
    @Autowired
    private NoRuleDetailService noRuleDetailService;

    @Autowired
    private ServiceService serviceService;

    @Autowired
    private NoManagementService noManagementService;

    @Value("${sms.oddNo.notice.code}")
    private String smsTemplateCode;

    @Autowired
    SysMailService sysMailService;

    @Autowired
    SysSmsService sysSmsService;

    @Autowired
    private NoWarnNoticeService noWarnNoticeService;

    @Autowired
    private VerifyRuleService verifyRuleService;

    @Autowired
    private ProductService productService;

    @Autowired
    private ProductGroupService productGroupService;

    private com.sz.common.base.logger.Logger logger = com.sz.common.base.logger.Logger.getLogger(this.getClass());

    @Override
    public int save(SerialNoRule serialNoRule) {
        if (! StringUtils.isEmpty(serialNoRule.getSerialNumber())) {
            serialNoRule.setNumberSection(generateNumberSection(serialNoRule));
        }
        dao.save(getSqlName("insertSelective") , serialNoRule);
        return serialNoRule.getId();
    }

    @Override
    public void save(SerialNoRuleDetailDto serialNoRuleDetailDto, int type) {
        validNoRuleDetailDtoData(serialNoRuleDetailDto,type);
        if (checkFormat(serialNoRuleDetailDto)) {
            SerialNoRule serialNoRule = new SerialNoRule();
            SerialNoRuleDetails serialNoRuleDetails = new SerialNoRuleDetails();
            BeanUtils.copyProperties(serialNoRuleDetailDto, serialNoRuleDetails);
            BeanUtils.copyProperties(serialNoRuleDetailDto, serialNoRule);
            serialNoRule.setType(type);
            serialNoRule.setStatus(RuleStatus.DISABLED);
            if (StringUtils.isNotEmpty(serialNoRuleDetailDto.getVerifyRule())) {
                VerificationCodeAlgorithm va = AlgorithmFactory.getClass(algorithmMap.get(serialNoRuleDetailDto.getVerifyRule()));
                if (va == null) {
                    throw Exceptions.bizException(BillNumberErrorCodes.ERROR_RULE_PARITY_RULE_INVALID);
                }
                else {
                    if (va.checkSerialNumber(serialNoRuleDetailDto.getSerialNumber())) {
                        save(serialNoRule);
                        serialNoRuleDetails.setRuleId(serialNoRule.getId());
                        noRuleDetailService.save(serialNoRuleDetails);
                    }
                    else {
                        throw Exceptions.bizException(BillNumberErrorCodes.ERROR_RULE_PARITY_RULE_NOT_SATISFIED);
                    }
                }
            } else {
                save(serialNoRule);
                serialNoRuleDetails.setType(type);
                serialNoRuleDetails.setRuleId(serialNoRule.getId());
                noRuleDetailService.save(serialNoRuleDetails);
            }
        } else {
            throw Exceptions.bizException(
                    BillNumberErrorCodes.ERROR_CHANNEL_RULE_FORMAT_ERROR);
        }
    }

    private void validNoRuleDetailDtoData(SerialNoRuleDetailDto serialNoRuleDetailDto, int type) {
        if (serialNoRuleDetailDto != null) {
            if (serialNoRuleDetailDto.getType() == null) {
                serialNoRuleDetailDto.setType(type);
            }
            //验证校验规则
            if (StringUtils.isEmpty(serialNoRuleDetailDto.getFormatRule())) {
                throw Exceptions.bizException(BillNumberErrorCodes.ERROR_CHANNEL_RULE_FORMATRULE_EMPTY);
            }

            //验证序列
            if (StringUtils.isEmpty(serialNoRuleDetailDto.getSerialNumber())) {
                throw Exceptions.bizException(BillNumberErrorCodes.ERROR_CHANNEL_RULE_SERIALNUMBER_EMPTY);
            }
            if (type == NumberType.CHANNEL_NUMBER) {
                //验证服务
                ServiceDto serviceDto = serviceService.findDtoActivedByUid(serialNoRuleDetailDto.getRefUid());
                if (serviceDto == null) {
                    throw Exceptions.bizException(BillNumberErrorCodes.ERROR_CHANNEL_RULE_SERVICE_NOT_EXIST);
                }
                else {
                    serialNoRuleDetailDto.setRefId(serviceDto.getSupplierId());
                }
            }
            else {
                //验证产品组不能为空
                if (serialNoRuleDetailDto.getRefId() == null) {
                    throw Exceptions.bizException(BillNumberErrorCodes.ERROR_WAYBILL_RULE_PRODUCT_GROUP_EMPTY);
                }
                long  number = WaybillPrefixCollectionConstants.waybillPrefixDtoList.stream().filter(e->
                        e.getProductGroupId() == serialNoRuleDetailDto.getRefId() && e.getPrefix().equals
                                (serialNoRuleDetailDto.getPrefix())).count();
                if (number != 1) {
                    throw Exceptions.bizException(BillNumberErrorCodes.ERROR_RULE_PREFIX_ERROR);
                }
            }
            //如果? 那么必须选一个校验位生成规则
            if (serialNoRuleDetailDto.getSerialNumber().contains(Symbols.SYMBOL_VERIFYCODE)) {
                if (StringUtils.isEmpty(serialNoRuleDetailDto.getVerifyRule())) {
                    throw Exceptions.bizException(BillNumberErrorCodes.ERROR_CHANNEL_RULE_VERIFY_RULE_EMPTY_ERROR);
                }
            }
            //此处形如AC123456C-AC223456C的单号应可通过
            if (! serialNoRuleDetailDto.getSerialNumber().contains(Symbols.SYMBOL_SEPERATOR)) {
                if (! serialNoRuleDetailDto.getSerialNumber().contains(Symbols.SYMBOL_LETTER) && ! serialNoRuleDetailDto.getSerialNumber().contains(Symbols.SYMBOL_DIGIT) && ! serialNoRuleDetailDto.getSerialNumber().contains(Symbols.SYMBOL_MIXED)) {
                    throw Exceptions.bizException(BillNumberErrorCodes.ERROR_CHANNEL_RULE_SERIAL_NUMBER_NOT_CONTAIN_WILD_CARD);
                }
            }

            if (serialNoRuleDetailDto.getSerialNumber().contains(Symbols.SYMBOL_SEPERATOR)) {
                if (serialNoRuleDetailDto.getSerialNumber().split(Symbols.SYMBOL_SEPERATOR).length > ARRAY_LENGTH) {
                    throw Exceptions.bizException(BillNumberErrorCodes.ERROR_RULE_SERIAL_NUMBER_NOT_MATCH_FORMAT);
                }
                else {
                    String[] str = serialNoRuleDetailDto.getSerialNumber().split(Symbols.SYMBOL_SEPERATOR);
                    String start = str[0];
                    String end = str[1];
                    if (start.compareTo(end) > 0) {
                        throw Exceptions.bizException(BillNumberErrorCodes.ERROR_RULE_SERIAL_NUMBER_NOT_MATCH_FORMAT);
                    }
                }
            }
        }
        else {
            throw Exceptions.bizException(SysErrorCodes.ERROR_COMMON , "数据不能为空");
        }
    }

    public boolean checkFormat(SerialNoRuleDetailDto serialNoRuleDetailDto) {
        //如果序列部分包含分隔符（此处序列号不包含前缀后缀及前缀后缀中的可变部分，是纯序列部分）
        if (serialNoRuleDetailDto.getSerialNumber().contains(Symbols.SYMBOL_SEPERATOR)) {
            String[] splitPatterns = serialNoRuleDetailDto.getSerialNumber().split(Symbols.SYMBOL_SEPERATOR);
            boolean flag = true;
            //校验切分段数
            if (splitPatterns.length != RANGE) {
                flag = false;
            }
            //校验每段是否非空
            if (StringUtils.isEmpty(splitPatterns[0]) || StringUtils.isEmpty(splitPatterns[1])) {
                flag = false;
            }
            //校验两段长度
            if (splitPatterns[0].length() != splitPatterns[1].length()) {
                flag = false;
            }
            //加前缀
            splitPatterns[0] = serialNoRuleDetailDto.getPrefix() + splitPatterns[0];
            splitPatterns[1] = serialNoRuleDetailDto.getPrefix() + splitPatterns[1];
            //若序列部分及格式规则均包含校验位
            if (serialNoRuleDetailDto.getSerialNumber().contains(Symbols.SYMBOL_VERIFYCODE) && serialNoRuleDetailDto.getFormat().contains(Symbols.SYMBOL_VERIFYCODE)) {
                //若两段的校验位位置与格式规则不一致
                if (splitPatterns[0].indexOf(Symbols.SYMBOL_VERIFYCODE) != serialNoRuleDetailDto.getFormat().replaceAll(Characters.BLANK, Characters.EMPTY_STRING).indexOf(Symbols.SYMBOL_VERIFYCODE) || splitPatterns[1].indexOf(Symbols.SYMBOL_VERIFYCODE) != serialNoRuleDetailDto.getFormat().replaceAll(Characters.BLANK, Characters.EMPTY_STRING).indexOf(Symbols.SYMBOL_VERIFYCODE)) {
                    flag = false;
                }
            }
            if (!flag) {
                return false;
            }
        } else {
            //若序列部分不包含分隔符且序列部分及格式规则均包含校验位
            if (serialNoRuleDetailDto.getSerialNumber().contains(Symbols.SYMBOL_VERIFYCODE) && serialNoRuleDetailDto.getFormat().contains(Symbols.SYMBOL_VERIFYCODE)) {
                //若序列部分+前缀之后的校验位位置与格式规则不一致
                if (serialNoRuleDetailDto.getSerialNumber().indexOf(Symbols.SYMBOL_SEPERATOR) != serialNoRuleDetailDto.getFormat().replaceAll(Characters.BLANK, Characters.EMPTY_STRING).indexOf(Symbols.SYMBOL_SEPERATOR)) {
                    return false;
                }
            }
        }
        //至此针对校验位的判断完毕，开始进行正则判断
        String patternNumber;
        String regex = verifyRuleService.selectRegexByRuleCode(serialNoRuleDetailDto.getFormatRule());
        //获取完整的号段定义
        patternNumber = (serialNoRuleDetailDto.getPrefix()
                + serialNoRuleDetailDto.getSerialNumber()
                + serialNoRuleDetailDto.getPostfix());
        //如果有分隔符，则分隔符前要带后缀，分隔符后要带前缀，保证前后两段整体格式的一致性
        if (serialNoRuleDetailDto.getSerialNumber().contains(Symbols.SYMBOL_SEPERATOR)) {
            patternNumber = patternNumber.replace(
                    Symbols.SYMBOL_SEPERATOR ,
                    serialNoRuleDetailDto.getPostfix()
                            + Symbols.SYMBOL_SEPERATOR
                            + serialNoRuleDetailDto.getPrefix());
        }
        //计算该号段的起止点和包含数量
        Map<String,String> channelNumberMap = SerialNumberRangeResolve.resolveRangeNew(serialNoRuleDetailDto
                .getPrefix(),serialNoRuleDetailDto.getSerialNumber(),serialNoRuleDetailDto.getPostfix());
        String startNumber = null;
        String endNumber = null;
        long range = 0;
        if (channelNumberMap != null) {
            startNumber =channelNumberMap.get(PatternParse.START_CHANNEL_NUMBER);
         endNumber = channelNumberMap.get(PatternParse.END_CHANNEL_NUMBER);
         range = Long.valueOf(channelNumberMap.get(PatternParse.CHANNEL_NUMBER_RANGE));
        }String formatStr =  patternNumber
                .replaceAll(Regexs.DOUBLE_SLASH + Symbols.SYMBOL_DIGIT,
                            String.valueOf(PatternParse.CHARACTER_INTEGER_ARRAY[0]))
                .replaceAll(Symbols.SYMBOL_LETTER,
                            String.valueOf(PatternParse.CHARACTER_INTEGER_ARRAY[10]))
                .replaceAll(Regexs.DOUBLE_SLASH + Symbols.SYMBOL_VERIFYCODE, String.valueOf(PatternParse.CHARACTER_INTEGER_ARRAY[0]));
        serialNoRuleDetailDto.setStartNumber(startNumber);
        serialNoRuleDetailDto.setEndNumber(endNumber);
        serialNoRuleDetailDto.setNumberSection(patternNumber);
        serialNoRuleDetailDto.setTotal(range);

        if (formatStr.contains(Symbols.SYMBOL_MIXED)) {
            String checkReplaceNumberStr = formatStr.replaceAll(Regexs.DOUBLE_SLASH + Symbols.SYMBOL_MIXED, String.valueOf(PatternParse.CHARACTER_INTEGER_ARRAY[0]));
            String checkReplaceCharacterStr = formatStr.replaceAll(Regexs.DOUBLE_SLASH + Symbols.SYMBOL_MIXED, String.valueOf(PatternParse.CHARACTER_INTEGER_ARRAY[10]));
            return checkIsMatches(checkReplaceCharacterStr, regex) && checkIsMatches(checkReplaceNumberStr, regex);
        }
        return checkIsMatches(formatStr, regex);
    }

    private boolean checkIsMatches(String checkStr , String regex){
        if (checkStr.contains(Symbols.SYMBOL_SEPERATOR)) {
            String[] forStrings = checkStr.trim().split(Symbols.SYMBOL_SEPERATOR);
            if (forStrings.length != ARRAY_LENGTH) {
                throw Exceptions.bizException(
                        BillNumberErrorCodes.ERROR_CHANNEL_RULE_FORMAT_ERROR);
            }
            else {
                return forStrings[0].matches(regex) && forStrings[1].matches(regex);
            }
        }else{
            return checkStr.matches(regex);
        }
    }

    @Override
    public void update(SerialNoRuleDetailDto serialNoRuleDetailDto, int id) {
        SerialNoRuleDetailDto currentResult = queryDetailsDtoById(id);
        if (currentResult == null) {
            throw Exceptions.bizException(BillNumberErrorCodes.ERROR_CHANNEL_RULES_NOT_FOUND_ERROR);
        }
        if (currentResult.getStatus() != RuleStatus.DISABLED) {
            throw Exceptions.bizException(
                    BillNumberErrorCodes.ERROR_CHANNEL_RULES_NOT_DISABLED);
        }
        else {
            serialNoRuleDetailDto.setId(id);
            validNoRuleDetailDtoData(serialNoRuleDetailDto, currentResult.getType());
            //如果该规则下尚未生成单号，则可以整体修改。否则只允许修改生成方式和提醒方式。
            if (!noManagementService.generateChannelNoByRule(serialNoRuleDetailDto.getId())) {
                if (checkFormat(serialNoRuleDetailDto)) {
                    SerialNoRuleDetails serialNoRuleDetails = new SerialNoRuleDetails();
                    SerialNoRule serialNoRule = new SerialNoRule();
                    BeanUtils.copyProperties(serialNoRuleDetailDto, serialNoRuleDetails);
                    BeanUtils.copyProperties(serialNoRuleDetailDto, serialNoRule);
                    SerialNoRuleDetails oldSerialNoRuleDetails = noRuleDetailService.findByRuleId(id);
                    serialNoRuleDetails.setId(oldSerialNoRuleDetails.getId());
                    serialNoRuleDetails.setRuleId(serialNoRule.getId());
                    serialNoRule.setStatus(currentResult.getStatus());
                    if (StringUtils.isNotEmpty(serialNoRuleDetailDto.getVerifyRule())) {
                        VerificationCodeAlgorithm va = AlgorithmFactory.getClass(algorithmMap.get(serialNoRuleDetailDto.getVerifyRule()));
                        if (va != null) {
                            if (va.checkSerialNumber(serialNoRule.getSerialNumber())) {
                                dao.update(getSqlName("updateByPrimaryKey") , serialNoRule);
                                noRuleDetailService.update(serialNoRuleDetails);
                            }
                            else {
                                throw Exceptions.bizException(BillNumberErrorCodes.ERROR_RULE_PARITY_RULE_NOT_SATISFIED);
                            }
                        }
                        else {
                            throw Exceptions.bizException(BillNumberErrorCodes.ERROR_RULE_PARITY_RULE_INVALID);
                        }
                    }
                    else {
                        dao.update(getSqlName("updateByPrimaryKey") , serialNoRule);
                        noRuleDetailService.update(serialNoRuleDetails);
                    }
                }
                else {
                    throw Exceptions.bizException(
                            BillNumberErrorCodes.ERROR_CHANNEL_RULE_FORMAT_ERROR);
                }
            }
            else {
                SerialNoRuleDetails serialNoRuleDetails = new SerialNoRuleDetails();
                BeanUtils.copyProperties(serialNoRuleDetailDto, serialNoRuleDetails);
                SerialNoRuleDetails oldSerialNoRuleDetails = noRuleDetailService.findByRuleId(id);
                serialNoRuleDetails.setId(oldSerialNoRuleDetails.getId());
                serialNoRuleDetails.setStartNumber(currentResult.getStartNumber());
                serialNoRuleDetails.setEndNumber(currentResult.getEndNumber());
                serialNoRuleDetails.setTotal(currentResult.getTotal());
                serialNoRuleDetails.setRuleId(id);
                noRuleDetailService.update(serialNoRuleDetails);
            }
        }

    }

    @Override
    public void updateRuleStatus(int id , int status) {
        ParamData pd = new ParamData();
        pd.put("id" , id);
        pd.put("status" , status);
        dao.update(getSqlName("updateStatus") , pd);
    }


    @Override
    public SerialNoRule find(int id) {
        return (SerialNoRule) dao.findForObject(getSqlName("query") , id);
    }

    @Override
    public QResultDto findRules(QueryParams queryParams,int type) {
        ParamData paramData = this.convertQueryParams(queryParams);
        List<SerialNoRuleListData> serialNoRuleListDataList = dao.findForList(getSqlName("findByParamListPage") , paramData , SerialNoRuleListData.class);
        if (serialNoRuleListDataList != null && serialNoRuleListDataList.size() > 0) {
            serialNoRuleListDataList.stream().forEach(e -> {
                if (e.getCreateMethod() == RuleCreateMethod.AUTO) {
                    e.setCreateMethodName(I18nMessageHelper.getI18nMessage(BillNumberMessagesCodes.MODULE_CHANNEL_RULE_CREATE_METHOD_AUTO , null));
                }
                else {
                    e.setCreateMethodName(I18nMessageHelper.getI18nMessage(BillNumberMessagesCodes.MODULE_CHANNEL_RULE_CREATE_METHOD_MANUAL , null));
                }
                if (e.getStatus() == RuleStatus.NOTENABLED) {
                    e.setStatusName(I18nMessageHelper.getI18nMessage(BillNumberMessagesCodes.MODULE_CHANNEL_RULE_STATUS_NOT_ENABLED , null));
                }
                if (e.getStatus() == RuleStatus.ENABLED) {
                    e.setStatusName(I18nMessageHelper.getI18nMessage(BillNumberMessagesCodes.MODULE_CHANNEL_RULE_STATUS_ENABLED , null));
                }
                if (e.getStatus() == RuleStatus.DISABLED) {
                    e.setStatusName(I18nMessageHelper.getI18nMessage(BillNumberMessagesCodes.MODULE_CHANNEL_RULE_STATUS_DISABLED , null));
                }
                if (type == NumberType.CHANNEL_NUMBER) {
                    ServiceDto service = serviceService.findDtoActivedByUid(e.getRefUid());
                    if (service != null) {
                        e.setRefUidName(service.getName());
                        e.setRefIdName(service.getSupplierName());
                    }
                }
                else {
                    if (StringUtils.isNotEmpty(e.getRefUid())) {
                        Product product = productService.findByUid(e.getRefUid());
                        if (product != null) {
                            e.setRefUidName(product.getName());
                        }
                    }
                    ProductGroup productGroup = productGroupService.findById(e.getRefId());
                    if (productGroup != null) {
                        e.setRefIdName(productGroup.getName());
                    }
                }
            });
        }
        QResultDto qResultDto = new QResultDto();
        qResultDto.setData(serialNoRuleListDataList);
        qResultDto.setPagination(paramData.getPagination());
        return qResultDto;
    }

    @Override
    public void enableRules(int id) {
        SerialNoRuleDetailDto channelNoRule = queryDetailsDtoById(id);
        if (channelNoRule.getStatus() == RuleStatus.ENABLED) {
            throw Exceptions.bizException(
                    BillNumberErrorCodes.ERROR_CHANNEL_RULE_UPDATE_STATUS_ENABLE);
        }
        updateRuleStatus(id , RuleStatus.ENABLED);
        if (channelNoRule.getCreateMethod() == RuleCreateMethod.AUTO) {
            //当前未使用数量 和阈值数量
            int currentRulesNoUsedTotal = noManagementService.getResidueNumberByServiceUidRuleIdAndBatchNo
                    (channelNoRule.getId());
            while(true) {
                if (currentRulesNoUsedTotal <= channelNoRule.getGenerateThreshold()) {
                    if (channelNoRule.getBatchNumber() != null && channelNoRule.getBatchNumber() > 0) {
                        currentRulesNoUsedTotal = currentRulesNoUsedTotal + channelNoRule.getBatchNumber();
                        noManagementService.generateChannelNo(channelNoRule , ChannelNoCreateMethod.AUTO);
                    }
                    else {
                        throw Exceptions.bizException(
                                BillNumberErrorCodes.ERROR_CHANNEL_BATCH_NUMBER_ERROR , 1);
                    }
                }
                else {
                    break;
                }
            }
        }
    }

    @Override
    public List<String> getExampleChannelNos(int id) {
        SerialNoRuleDetailDto channelNoRule = queryDetailsDtoById(id);
        List<String> result = new ArrayList<>();
        if (channelNoRule != null) {
            long exampleNumber =  channelNoRule.getTotal()>EXAMPLE_NUMBERS? EXAMPLE_NUMBERS:channelNoRule
                    .getTotal();
            channelNoRule.setFormat(channelNoRule.getFormat().replaceAll(Characters.BLANK, Characters.EMPTY_STRING));
            if (StringUtils.isNotEmpty(channelNoRule.getVerifyRule())) {
                VerificationCodeAlgorithm va = AlgorithmFactory.getClass(algorithmMap.get(channelNoRule.getVerifyRule()));
                for(int i = 0; i < exampleNumber; i++) {
                    String current = va.generateVerificationCode(
                        PatternStringToNumber.generateChannelNumber(channelNoRule.getStartNumber(),channelNoRule
                                .getPrefix(),channelNoRule.getSerialNumber(),channelNoRule.getPostfix(),i),
                            channelNoRule.getNumberSection(),
                            channelNoRule.getFormat());
                    result.add(current);
                }
            } else {
                for(int i = 0; i < exampleNumber; i++) {
                    String current = PatternStringToNumber.generateChannelNumber(channelNoRule.getStartNumber(),channelNoRule
                            .getPrefix(),channelNoRule.getSerialNumber(),channelNoRule.getPostfix(),i);
                    result.add(current);
                }
            }
        } else {
            throw Exceptions.bizException(
                    BillNumberErrorCodes.ERROR_CHANNEL_RULES_NOT_FOUND_ERROR);
        }
        return result;
    }

    @Override
    public void disableRules(int id) {
        updateRuleStatus(id , RuleStatus.DISABLED);
    }

    @Override
    public SerialNoRuleDetailDto queryDetailsDtoById(int id) {
        SerialNoRuleDetailDto serialNoRuleDetailDto = (SerialNoRuleDetailDto) dao.findForObject(getSqlName("queryDetaisById") , id);
        if (serialNoRuleDetailDto == null) {
            throw Exceptions.bizException(
                    BillNumberErrorCodes.ERROR_CHANNEL_RULES_NOT_FOUND_ERROR);
        }
        else {
            if (serialNoRuleDetailDto.getType().intValue() == NumberType.CHANNEL_NUMBER) {
                com.sz.biz.logistics.core.entity.Service service = serviceService.findActivedByUid
                        (serialNoRuleDetailDto.getRefUid());
                if (service != null) {
                    serialNoRuleDetailDto.setRefUidName(service.getName());
                    serialNoRuleDetailDto.setRefUidCode(service.getCode());
                }
                AlgorithmFactory.getAlgorithmInfoDtos(NumberType.CHANNEL_NUMBER).forEach(e -> copyAlgorithmInfo(serialNoRuleDetailDto, e));
            }
            else {
                ProductGroup productGroup = productGroupService.findById(serialNoRuleDetailDto.getRefId());
                serialNoRuleDetailDto.setRefName(productGroup != null ? productGroup.getName() : "");
                if (StringUtils.isNotEmpty(serialNoRuleDetailDto.getRefUid())) {
                    Product product = productService.findByUid(serialNoRuleDetailDto.getRefUid());
                    serialNoRuleDetailDto.setRefUidName(product != null ? product.getName() : "");
                    serialNoRuleDetailDto.setRefUidCode(product != null ? product.getCode() : "");
                }
                AlgorithmFactory.getAlgorithmInfoDtos(NumberType.WAYBILL_NUMBER).forEach(e -> copyAlgorithmInfo(serialNoRuleDetailDto, e));
            }
            serialNoRuleDetailDto.setIsUpdateAll(!noManagementService.generateChannelNoByRule(serialNoRuleDetailDto.getId()));
        }
        return serialNoRuleDetailDto;
    }
    
    private static void copyAlgorithmInfo(SerialNoRuleDetailDto serialNoRuleDetailDto, AlgorithmInfoDto e) {
        if (e.getCode().equalsIgnoreCase(serialNoRuleDetailDto.getVerifyRule())) {
            serialNoRuleDetailDto.setVerifyName(e.getName());
            serialNoRuleDetailDto.setValidateLength(e.getValidateLength());
            serialNoRuleDetailDto.setVerifyCodeIndex(e.getVerifyCodeIndex());
            serialNoRuleDetailDto.setExample(e.getExample());
        }
    }
    
    @Override
    public long generateChannelNo(SerialNoGenerateDto serialNoGenerateDto, int type) {
        List<SerialNoRule> serialNoRules;
        if (type == NumberType.CHANNEL_NUMBER) {
            serialNoRules = queryByServiceUid(serialNoGenerateDto.getRefUid());
        }
        else {
            serialNoRules = queryByProductGroupId(serialNoGenerateDto.getRefId() , serialNoGenerateDto.getRefUid());
        }
        int count = serialNoGenerateDto.getNumbers();
        if (serialNoRules == null || serialNoRules.size() == 0) {
            throw Exceptions.bizException(
                    BillNumberErrorCodes.ERROR_CHANNEL_NOT_RULES);
        }
        List<ManualSetupSectionDto> manualSetupSectionDtoList = new ArrayList<>();
        Map<String, String> res;
        for (SerialNoRule serialNoRule : serialNoRules) {
            ManualSetupSectionDto manualSetupSectionDto = new ManualSetupSectionDto();
            manualSetupSectionDto.setRuleId(serialNoRule.getId());

            manualSetupSectionDto.setRefUid(serialNoRule.getRefUid());
            manualSetupSectionDto.setRefId(serialNoRule.getRefId());
            manualSetupSectionDto.setFormat(verifyRuleService.selecByRuleCode(serialNoRule.getFormatRule()).getFormat());
            manualSetupSectionDto.setRefCustomer(serialNoGenerateDto.getCustomerId());
            manualSetupSectionDto.setPattern(serialNoRule.getNumberSection());
            manualSetupSectionDto.setPostFix(serialNoRule.getPostfix());
            manualSetupSectionDto.setSerialNumber(serialNoRule.getSerialNumber());
            manualSetupSectionDto.setPrefix(serialNoRule.getPrefix());
            manualSetupSectionDto.setVerifyRule(serialNoRule.getVerifyRule());

            res = SerialNumberRangeResolve.resolveRangeNew(serialNoRule.getPrefix(),serialNoRule.getSerialNumber(),
                    serialNoRule.getPostfix());
            String temp = noManagementService.findMaxNumberByRuleId(serialNoRule.getId());
            if (!StringUtils.isEmpty(manualSetupSectionDto.getVerifyRule())) {
                if (temp != null) {
                    temp = temp.substring(0, manualSetupSectionDto.getPattern().indexOf(Symbols.SYMBOL_VERIFYCODE))
                        + Symbols.SYMBOL_VERIFYCODE
                        + temp.substring(manualSetupSectionDto.getPattern().indexOf(Symbols.SYMBOL_VERIFYCODE) + 1);
                }
            }
            if (res != null) {
                manualSetupSectionDto.setStartNumber(res.get(PatternParse.START_CHANNEL_NUMBER));
                manualSetupSectionDto.setEndNumber(res.get(PatternParse.END_CHANNEL_NUMBER));
            }
            //db中没有生成的数据
            if (StringUtils.isEmpty(temp)) {
                manualSetupSectionDto.setStartNumber(manualSetupSectionDto.getStartNumber());
                manualSetupSectionDtoList.add(manualSetupSectionDto);
            }
            else {
                if (SerialNumberRangeResolve.offsetCalculate(manualSetupSectionDto.getEndNumber() , serialNoRule
                        .getPrefix(),serialNoRule.getSerialNumber(),serialNoRule.getPostfix()) >
                        SerialNumberRangeResolve.offsetCalculate(temp , serialNoRule
                                .getPrefix(),serialNoRule.getSerialNumber(),serialNoRule.getPostfix())) {
                    String tempNext = PatternStringToNumber.generateChannelNumber(temp,serialNoRule.getPrefix(),serialNoRule
                            .getSerialNumber(),serialNoRule.getPostfix(),1);
                    manualSetupSectionDto.setStartNumber(tempNext);
                    manualSetupSectionDtoList.add(manualSetupSectionDto);
                }
            }
        }
        Collections.sort(manualSetupSectionDtoList);
        long generateTotal =0L;
        Map<ManualSetupSectionDto, List<String>> paramMap = null;
        for (ManualSetupSectionDto manualSetupSectionDto : manualSetupSectionDtoList) {
            paramMap = new HashMap<>(1);
            List<String> channelNoList = new ArrayList<>();
            Long temp = SerialNumberRangeResolve.offsetCalculate(manualSetupSectionDto.getEndNumber(),
                    manualSetupSectionDto.getPrefix(),manualSetupSectionDto.getSerialNumber(),manualSetupSectionDto.getPostFix())
                        - SerialNumberRangeResolve.offsetCalculate(manualSetupSectionDto.getStartNumber(),manualSetupSectionDto.getPrefix(),
                    manualSetupSectionDto.getSerialNumber(),manualSetupSectionDto.getPostFix());
            // temp 加上起始位置的数据
            temp++;
            if (count != 0) {
                if (temp >= count) {
                    addManualSetupSectionDto(channelNoList, manualSetupSectionDto, count);
                    paramMap.put(manualSetupSectionDto, channelNoList);
                    generateTotal=generateTotal+count;
                    count = 0;
                } else {
                    if (temp != 0) {
                        addManualSetupSectionDto(channelNoList , manualSetupSectionDto , temp);
                        paramMap.put(manualSetupSectionDto , channelNoList);
                        count -= temp;
                        generateTotal= generateTotal+ temp;
                    }
                }
            }
            noManagementService.manualBatchInsert(paramMap,type);
        }
        return generateTotal;
    }
    
    private void addManualSetupSectionDto(List<String> channelNoList, ManualSetupSectionDto manualSetupSectionDto, long count) {
        for (long i = 0; i < count; i++) {
            String rawString = PatternStringToNumber.generateChannelNumber(manualSetupSectionDto.getStartNumber(),manualSetupSectionDto
                    .getPrefix(),manualSetupSectionDto.getSerialNumber(),manualSetupSectionDto.getPostFix(),i);
            if (!StringUtils.isEmpty(manualSetupSectionDto.getVerifyRule())) {
                if (rawString != null) {
                    rawString = rawString.substring(0, manualSetupSectionDto.getPattern().indexOf(Symbols.SYMBOL_VERIFYCODE))
                        + Symbols.SYMBOL_VERIFYCODE
                        + rawString.substring(manualSetupSectionDto.getPattern().indexOf(Symbols.SYMBOL_VERIFYCODE) + 1);
                }
    
                VerificationCodeAlgorithm va = AlgorithmFactory.getClass(algorithmMap.get(manualSetupSectionDto.getVerifyRule()));
                if (va != null) {
                    rawString = va.generateVerificationCode(rawString , manualSetupSectionDto.getPattern() , manualSetupSectionDto.getFormat().replaceAll(SEPERATOR_SPACE , Characters.EMPTY_STRING));
                }
            }
            channelNoList.add(rawString);
        }
    }
    
    @Override
    public List<SerialNoRule> queryByServiceUid(String serviceUid) {
        if (StringUtils.isEmpty(serviceUid)) {
            return null;
        }
        ParamData pd = new ParamData();
        pd.put("refUid",serviceUid);
        return dao.findForList(getSqlName("queryByServiceUid") , serviceUid,SerialNoRule.class);
    }

    @Override
    public List<SerialNoRule> queryByProductGroupId(Integer productGroupId, String productId) {
        if (StringUtils.isEmpty(productGroupId)) {
            return null;
        }
        ParamData pd = new ParamData();
        if(StringUtils.isNotEmpty(productId)){
            pd.put("refUid",productId);
        }
        pd.put("refId",productGroupId);
        return dao.findForList(getSqlName("queryByProductGroupIdAndProductId"), pd,
                SerialNoRule.class);
    }

    @Override
    public QResultDto findService(QueryParams queryParams) {
        ParamData paramData =  this.convertQueryParams(queryParams);
        List<com.sz.biz.logistics.serialno.dto.ServiceDto> list = dao.findForList(getSqlName
                ("queryServiceByTypeAndStatusListPage") , paramData , com.sz.biz.logistics.serialno.dto.ServiceDto
                .class);
        QResultDto resultDto = new QResultDto();
        resultDto.setData(list);
        resultDto.setPagination(paramData.getPagination());
        return resultDto;
    }

    @Override
    public List<SerialNoRuleDetailDto> findAllRulesDetailByType(int type) {
        return dao.findForList(getSqlName("findAllRulesDetailByType"),type,SerialNoRuleDetailDto.class);
    }

    @Override
    public QResultDto findProductByQueryParams(QueryParams params) {
        ParamData paramData =this.convertQueryParams(params);
        List<Product> list =  dao.findForList(getSqlName("findIncludeProductByParamListPage"),paramData,Product.class);
        QResultDto q = new QResultDto();
        q.setData(list);
        q.setPagination(paramData.getPagination());
        return q;
    }

    private String generateNumberSection(SerialNoRule serialNoRule) {
        if (serialNoRule != null) {
            StringBuffer sb = new StringBuffer();
            if (StringUtils.isEmpty(serialNoRule.getSerialNumber())) {
                return null;
            }
            if (! StringUtils.isEmpty(serialNoRule.getPrefix())) {
                sb.append(serialNoRule.getPrefix());
            }
            if (! StringUtils.isEmpty(serialNoRule.getSerialNumber())) {
                sb.append(serialNoRule.getSerialNumber());
            }
            if (! StringUtils.isEmpty(serialNoRule.getPostfix())) {
                sb.append(serialNoRule.getPostfix());
            }
            if(serialNoRule.getSerialNumber().contains(Symbols.SYMBOL_SEPERATOR)){
             return sb.toString().replace(Symbols.SYMBOL_SEPERATOR, serialNoRule.getPostfix()+ Symbols.SYMBOL_SEPERATOR+ serialNoRule.getPrefix());
            }
            return sb.toString();
        }
        return "";
    }

    public void sendEmail(SerialNoRuleDetailDto serialNoRuleDetailDto, String batchNumber) {
        if (ChannelNoticeType.EMAIL.equals(serialNoRuleDetailDto.getNoticeType())) {
            if (! StringUtils.isEmpty(serialNoRuleDetailDto.getReceiver())) {
                List<String> emails = geneEmailAddress(serialNoRuleDetailDto.getReceiver());
                if (emails != null && emails.size() > 0) {
                    Map<String,Object> emailBodyMap = new HashMap<>(1);
                    if(serialNoRuleDetailDto.getType().intValue() == NumberType.CHANNEL_NUMBER){
                        emailBodyMap.put("type" , serialNoRuleDetailDto.getRefUidName());
                        emailBodyMap.put("rule" , serialNoRuleDetailDto.getNumberSection());
                        emailBodyMap.put("notype" , "渠道单号");
                    }else {
                        emailBodyMap.put("type" , StringUtils.isEmpty(serialNoRuleDetailDto.getRefUid())?
                                serialNoRuleDetailDto.getRefName():serialNoRuleDetailDto.getRefUidName());
                        emailBodyMap.put("rule" , serialNoRuleDetailDto.getNumberSection());
                        emailBodyMap.put("notype" , "运单号");
                    }
                    String emailBody = JsonUtils.toJSONString(emailBodyMap);
                    String[] strings = new String[emails.size()];
                    for(int i = 0; i < emails.size(); i++) {
                        strings[i] = emails.get(i);
                    }
                    int id = (int) sysMailService.createMail("sys.logistics.master@hna.com" , strings , "单号状态通知" ,
                            emailBody , TemplateConstants.NO_NOTICE);
                    SerialNoWarnNotice serialNoWarnNotice = new SerialNoWarnNotice();
                    serialNoWarnNotice.setBatchNumber(batchNumber);
                    serialNoWarnNotice.setIsSent(true);
                    serialNoWarnNotice.setType(serialNoRuleDetailDto.getType());
                    serialNoWarnNotice.setNoticeType(serialNoRuleDetailDto.getNoticeType());
                    serialNoWarnNotice.setReceiver(serialNoRuleDetailDto.getReceiver());
                    serialNoWarnNotice.setRefMessageId(id);
                    serialNoWarnNotice.setRuleId(serialNoRuleDetailDto.getId());
                    serialNoWarnNotice.setSendTime(new Date());
                    serialNoWarnNotice.setTemplate(TemplateConstants.NO_NOTICE);
                    noWarnNoticeService.save(serialNoWarnNotice);
                }
            }
        }
    }


    @Override
    public void sendMessage(SerialNoRuleDetailDto serialNoRuleDetailDto, String batchNumber) {
        sendEmail(serialNoRuleDetailDto, batchNumber);
        sendSMS(serialNoRuleDetailDto, batchNumber);
    }

    @Override
    public SerialNoImportFileResultDto readImportExcelFromStream(InputStream inputStream , String serviceUid) {
        com.sz.biz.logistics.core.entity.Service service = serviceService.findActivedByUid(serviceUid);
        if (service == null) {
            throw Exceptions.bizException(
                    BillNumberErrorCodes.ERROR_CHANNEL_RULE_SERVICE_NOT_EXIST);
        }

        SerialNoImportFileResultDto serialNoImportFileResultDto = new SerialNoImportFileResultDto();
        OPCPackage pkg;
        XSSFWorkbook wb;
        try {
            pkg = OPCPackage.open(inputStream);
            wb = new XSSFWorkbook(pkg);
        } catch(Exception e) {
            throw Exceptions.bizException(SysErrorCodes.ERROR_FILE_ILLEGAL);
        }
        Sheet sheet = wb.getSheetAt(0);
        if (null == sheet) {
            throw Exceptions.bizException(
                    BillNumberErrorCodes.ERROR_CHANNEL_IMPORT_EXCEL_LEAST_ONE_PAGE);
        }
        //获取有效条数
        int rows = sheet.getPhysicalNumberOfRows();
        if (rows > MAX_SIZE) {
            throw Exceptions.bizException(
                    BillNumberErrorCodes.ERROR_CHANNEL_IMPORT_EXCEL_MAX_NUMBER , MAX_SIZE);
        }
        List<String> channelNos = new ArrayList<>();
        for(Row row : sheet) {
            String rowValue = getChannelNo(row);
            if (! StringUtils.isEmpty(rowValue)) {
                if (! channelNos.contains(rowValue)) {
                    channelNos.add(rowValue);
                }
            }
        }
        List<SerialNoRuleRangeDto> serialNoRuleRangeDtos = searchRuleBoundariesByServiceUid(serviceUid);

        if (serialNoRuleRangeDtos == null || serialNoRuleRangeDtos.size() == 0) {
            channelNos.stream().forEach(e -> {
                serialNoImportFileResultDto.getSuccess().add(new SerialNoImportSuccessResultDto(e , 0));
                serialNoImportFileResultDto.getResult().add(new SerialNoImportResultDto(e , 0 , true));
            });
        }
        else {
            //优先算出使用哪几种规则 然后按照规则 批次导入.
            for(String channelNo : channelNos) {
                int times = 0;
                out:
                for(SerialNoRuleRangeDto serialNoRuleRangeDto : serialNoRuleRangeDtos) {
                    try {
                        times++;
                        if (StringUtils.isNotEmpty(serialNoRuleRangeDto.getVerifyRule())) {
                            VerificationCodeAlgorithm va = AlgorithmFactory.getClass(algorithmMap.get(serialNoRuleRangeDto.getVerifyRule()));
                            if (va != null && va.validityCheck(channelNo , serialNoRuleRangeDto.getPattern() ,
                                    serialNoRuleRangeDto.getFormat().replaceAll(" " , Characters.EMPTY_STRING))) {
                                if (SerialNumberRangeResolve.isRangeTest(channelNo ,
                                        serialNoRuleRangeDto.getPrefix(),serialNoRuleRangeDto.getSerialNumber(),
                                        serialNoRuleRangeDto.getPostfix())) {
                                    serialNoImportFileResultDto.getSuccess().add(new SerialNoImportSuccessResultDto(channelNo , serialNoRuleRangeDto.getRuleId()));
                                    serialNoImportFileResultDto.getResult().add(new SerialNoImportResultDto(channelNo , serialNoRuleRangeDto.getRuleId() , Boolean.TRUE));
                                    times--;
                                    break out;
                                }
                            }
                        }
                        else {
                            if (SerialNumberRangeResolve.isRangeTest(channelNo ,
                                    serialNoRuleRangeDto.getPrefix(),serialNoRuleRangeDto.getSerialNumber(),
                                    serialNoRuleRangeDto.getPostfix())) {
                                serialNoImportFileResultDto.getSuccess().add(new SerialNoImportSuccessResultDto(channelNo , serialNoRuleRangeDto.getRuleId()));
                                serialNoImportFileResultDto.getResult().add(new SerialNoImportResultDto(channelNo , serialNoRuleRangeDto.getRuleId() , Boolean.TRUE));
                                times--;
                                break out;
                            }
                        }

                    } catch(Exception e) {
                        logger.error(e.getMessage());
                    }
                }
                if (times == serialNoRuleRangeDtos.size()) {
                    serialNoImportFileResultDto.getResult().add(new SerialNoImportResultDto(channelNo , 0 , Boolean.FALSE));
                }
            }
        }
        return serialNoImportFileResultDto;
    }
    
    @Override
    public List<SerialNoRuleRangeDto> searchRuleBoundariesByServiceUid(String serviceUid) {
        if (StringUtils.isEmpty(serviceUid)) {
            return null;
        }
        return  dao.findForList(getSqlName("searchRuleBoundariesByServiceUid"),
                serviceUid, SerialNoRuleRangeDto.class);
        }
    
    
    private String getChannelNo(Row row) {
        Cell cell = row.getCell(0);
        String value = getValue(cell);
        if (value != null) {
            return value.trim();
        }
        return null;
    }


    public void sendSMS(SerialNoRuleDetailDto serialNoRuleDetailDto, String batchNumber) {
        if (ChannelNoticeType.SMS.equals(serialNoRuleDetailDto.getNoticeType())) {
            List<String> phones = genePhoneNumbers(serialNoRuleDetailDto.getReceiver());
            if (phones != null && phones.size() > 0) {
                Map<String,Object> smsBodyMap = new HashMap<>(1);
                if (serialNoRuleDetailDto.getType().intValue() == NumberType.CHANNEL_NUMBER) {
                    smsBodyMap.put("type" , serialNoRuleDetailDto.getRefUidName());
                    smsBodyMap.put("rule" , serialNoRuleDetailDto.getNumberSection());
                    smsBodyMap.put("noType" , "渠道单号");
                }
                else {
                    smsBodyMap.put("type" , StringUtils.isEmpty(serialNoRuleDetailDto.getRefUid())?
                            serialNoRuleDetailDto.getRefName():serialNoRuleDetailDto.getRefUidName());
                    smsBodyMap.put("rule" , serialNoRuleDetailDto.getNumberSection());
                    smsBodyMap.put("noType" , "运单号");
                }
                String smsBody = JsonUtils.toJSONString(smsBodyMap);
                int id = sysSmsService.createSMS(AliSMSPhonesUtil.getPhones(phones) , smsBody , smsTemplateCode);
                SerialNoWarnNotice serialNoWarnNotice = new SerialNoWarnNotice();
                serialNoWarnNotice.setBatchNumber(batchNumber);
                serialNoWarnNotice.setIsSent(true);
                serialNoWarnNotice.setNoticeType(serialNoRuleDetailDto.getNoticeType());
                serialNoWarnNotice.setType(serialNoRuleDetailDto.getType());
                serialNoWarnNotice.setReceiver(AliSMSPhonesUtil.getPhones(phones));
                serialNoWarnNotice.setRefMessageId(id);
                serialNoWarnNotice.setRuleId(serialNoRuleDetailDto.getId());
                serialNoWarnNotice.setSendTime(new Date());
                serialNoWarnNotice.setTemplate(smsTemplateCode);
                noWarnNoticeService.save(serialNoWarnNotice);
            }
        }
    }

    public static List<String> geneEmailAddress(String receiver) {
        if (StringUtils.isEmpty(receiver)) {
            return null;
        }
        return Stream.of(receiver.split("[;；]")).filter(StringUtils::isNotEmpty).filter(EmailValid::checkEmail).collect
                (Collectors.toList());
    }

    public static List<String> genePhoneNumbers(String receiver) {
        if (StringUtils.isEmpty(receiver)) {
            return null;
        }
        return Stream.of(receiver.split("[;；]")).filter(StringUtils::isNotEmpty).filter(StringUtils::isNumber).filter
                (TelPhoneValid::isPhoneNumber).collect
                (Collectors.toList());
    }

    private String getValue(Cell cell) {
        String cellValue;
        switch (cell.getCellTypeEnum()) {
            case STRING:
                cellValue = cell.getRichStringCellValue().getString();
                break;
            default:
                cellValue = null;
                break;
        }

        return cellValue;
    }

    @Override
    protected String getMapperNamespace() {
        return "SerialNoRuleMapper";
    }

}
